package com.scalar.chiptrack.utils;

import com.scalar.ScalarException;
import com.scalar.chiptrack.operations.reports.DBColumn;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

/**
 * Created by IntelliJ IDEA.
 * User: munugoti
 * Date: 25 May, 2009
 * Time: 12:33:06 PM
 * To change this template use File | Settings | File Templates.
 */
public final class ExcelUtils {
    private Map<String, ArrayList<Range>> mergeIdxMap = new HashMap<String, ArrayList<Range>>();
    private Workbook workbook;
    public CellStyle CELL_STYLE_TITLE;
    public CellStyle CELL_STYLE_HEADER;
    public CellStyle CELL_STYLE_TEXT;
    public CellStyle CELL_STYLE_NUMBER;
    public CellStyle CELL_STYLE_NUMBER_TOTAL;
    public CellStyle CELL_STYLE_DATE;
    public CellStyle CELL_STYLE_FLOAT;
    public CellStyle CELL_STYLE_FLOAT_PER;
    public CellStyle CELL_STYLE_NUMERIC;
    public CellStyle CELL_STYLE_CURRENCY_$;
    public CellStyle CELL_STYLE_CURRENCY_$_TOTAL;
    public CellStyle CELL_STYLE_CURRENCY_FORMATTED_$; //Eg. $128,323.20

    public ExcelUtils() {

    }

    public ExcelUtils(Workbook workbook) {
        this.workbook = workbook;
        initialize();
    }

    public static void generateExcelReport(Workbook workbook, String fileName, HttpServletResponse response) throws Exception {
        ByteArrayOutputStream bos = null;
        try {
            bos = new ByteArrayOutputStream();

            if ( fileName.toUpperCase().indexOf(".XLS") != -1 )
            {
                fileName = fileName.substring(0, fileName.toUpperCase().indexOf(".XLS"));
            }
            if ( "xlsx".equalsIgnoreCase(ConfigManager.getXLSWorkBookFormat() ) )
            {
                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                fileName = fileName + ".xlsx";
            }
            else
            {
                response.setContentType("application/vnd.ms-excel");
                fileName = fileName+ ".xls";
            }

            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ";");
            if (workbook != null) {
                workbook.write(bos);
                bos.writeTo(response.getOutputStream());
            }
            response.getOutputStream().flush();
        }
        catch (Exception innerExcep) {
            String stackTrace = innerExcep.toString();
            if (stackTrace.indexOf("socket write error") > -1 || stackTrace.indexOf("Socket closed") > -1) {
                System.out.println("## Export to Excel ClientAbortException ##");
                //Logger.error( "## Export to Excel ClientAbortException ##", innerExcep );
            } else {
                throw innerExcep;
            }
        }
        finally {
            try {
                bos.close();
            } catch (IOException ignore) {
            }
            response.getOutputStream().close();
        }

    }

    public static void generateCSVReport(String csvString, String fileName, HttpServletResponse response) throws Exception {
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ";");
            response.getOutputStream().print(csvString);
            csvString = null;
            response.getOutputStream().flush();
        }
        catch (Exception innerExcep) {
            String stackTrace = innerExcep.toString();
            if (stackTrace.indexOf("socket write error") > -1 || stackTrace.indexOf("Socket closed") > -1) {
                System.out.println("## Export to CSV ClientAbortException ##");
                //Logger.error( "## Export to Excel ClientAbortException ##", innerExcep );
            } else {
                throw innerExcep;
            }
        }
        finally {
            response.getOutputStream().close();
        }

    }

    public static ArrayList<ArrayList> getAllResultSetRows(ResultSet rset, boolean headingsRequired) throws Exception {
        ArrayList<ArrayList> lotInfoList = new ArrayList<ArrayList>();
        ArrayList<String> hearderList = new ArrayList<String>();
        ArrayList<DBColumn> colList = null;
        DBColumn dbColumn = null;

        int colCount = rset.getMetaData().getColumnCount();

        if (headingsRequired) {
            for (int j = 1; j <= colCount; j++) {
                hearderList.add(rset.getMetaData().getColumnName(j));
            }
            lotInfoList.add(hearderList);
        }
        while (rset.next()) {
            colList = new ArrayList<DBColumn>();
            for (int i = 1; i <= colCount; i++) {
                dbColumn = new DBColumn(rset.getMetaData().getColumnName(i), rset.getObject(i), rset.getMetaData().getColumnType(i));
                colList.add(dbColumn);
            }
            lotInfoList.add(colList);
        }

        return lotInfoList;
    }

    public static ArrayList<ArrayList> getAllEditedResultSetRows(ResultSet rset, boolean headingsRequired, String headings) throws Exception {
        ArrayList<ArrayList> lotInfoList = new ArrayList<ArrayList>();
        ArrayList<String> hearderList = new ArrayList<String>();
        ArrayList<DBColumn> colList = null;
        DBColumn dbColumn = null;

        String psTypes[] = headings.split(",");
        int colCount = rset.getMetaData().getColumnCount();
        int pstypeCount = psTypes.length;

        if (headingsRequired) {
            for (int j = 1; j <= colCount; j++) {
                if ("QTY STR".equalsIgnoreCase(rset.getMetaData().getColumnName(j))) {
                    for (int ps = 0; ps < pstypeCount; ps++) {
                        if (psTypes[ps] != null && psTypes[ps].trim().length() > 0) {
                            hearderList.add(psTypes[ps]);
                        }
                    }
                } else {
                    hearderList.add(rset.getMetaData().getColumnName(j));
                }
            }
            lotInfoList.add(hearderList);
        }
        while (rset.next()) {
            colList = new ArrayList<DBColumn>();
            for (int i = 1; i <= colCount; i++) {
                if ("QTY STR".equalsIgnoreCase(rset.getMetaData().getColumnName(i))) {
                    String psTyeData[] = rset.getObject(i).toString().split(",");

                    for (int ps = 0; ps < psTyeData.length; ps++) {
                        if (psTyeData[ps] != null && psTyeData[ps].trim().length() > 0) {
                            dbColumn = new DBColumn(psTypes[ps - 1], Integer.parseInt(psTyeData[ps]), Types.INTEGER);
                            colList.add(dbColumn);
                        }
                    }
                } else {
                    dbColumn = new DBColumn(rset.getMetaData().getColumnName(i), rset.getObject(i), rset.getMetaData().getColumnType(i));
                    colList.add(dbColumn);
                }

            }
            lotInfoList.add(colList);
        }

        return lotInfoList;
    }

    public static String getCSVString(ResultSet rset, boolean isHeadersRequired) throws ScalarException {
        StringBuffer sb = new StringBuffer();
        try {
            int colCount = rset.getMetaData().getColumnCount();
            if (isHeadersRequired) {
                //For Setting the HEADER LINE
                for (int j = 1; j <= colCount; j++) {
                    sb.append("\"");
                    sb.append(rset.getMetaData().getColumnName(j));
                    sb.append("\"");
                    sb.append(",");
                }
                sb.append("\r\n");
            }
            //build a row for each entry
            while (rset.next()) {
                String value = null;
                for (int i = 1; i <= colCount; i++) {
                    sb.append("\"");

                    if (rset.getMetaData().getColumnType(i) == java.sql.Types.TIMESTAMP) {
                        value = StringUtils.formatDateToString(rset.getTimestamp(i), "MM/dd/yyyy HH:mm:ss");
                    } else {
                        value = rset.getString(i);
                    }
                    sb.append(value == null ? "" : value.replaceAll("\"", "\"\""));
                    sb.append("\"");
                    sb.append(",");
                }
                sb.append("\r\n");
            }
        }
        catch (SQLException e) {
            throw new ScalarException("Could not get CSV String from ResultSet", e);
        }
        return sb.toString();
    }

    /**
     * Returns the cell object at the specified cell in the row.<br>
     * It creats cell if doesn't exists.
     *
     * @param row        row object from which cell object has to be picked
     * @param cellNumber starts from 0.
     * @return
     */
    public Cell getCellAt(Row row, int cellNumber) {
        Cell cell = null;
        cell = row.getCell(cellNumber);
        if (cell == null) {
            cell = row.createCell(cellNumber);
        }
        return cell;
    }

    public void setCellValue(Cell cell, RichTextString value, CellStyle cellStyle) {
        if (cellStyle != null) {
            cell.setCellStyle(cellStyle);
        }
        cell.setCellValue(value);
    }

    public void setCellValue(Row row, int cellNo, Date value, CellStyle cellStyle) {
        Cell cell = row.createCell(cellNo);
        if (cellStyle != null) {
            cell.setCellStyle(cellStyle);
        }
        cell.setCellValue(value);
    }

    public void setCellValue(Row row, int cellNo, RichTextString value, CellStyle cellStyle) {
        Cell cell = row.createCell(cellNo);
        if (cellStyle != null) {
            cell.setCellStyle(cellStyle);
        }
        cell.setCellValue(value);
    }

    public void setCellValue(Row row, int cellNo, int value, CellStyle cellStyle, int cellType) {
        Cell cell = row.createCell(cellNo);
        if (cellStyle != null) {
            cell.setCellStyle(cellStyle);
        }
        cell.setCellValue(value);
        cell.setCellType(cellType);
    }

    public void setCellValue(Row row, int cellNo, double value, CellStyle cellStyle, int cellType) {
        Cell cell = row.createCell(cellNo);
        if (cellStyle != null) {
            cell.setCellStyle(cellStyle);
        }
        cell.setCellValue(value);
        cell.setCellType(cellType);
    }

    public void setCellFormula(Row row, int cellNo, String formula, CellStyle cellStyle) {
        Cell cell = row.createCell(cellNo);
        if (cellStyle != null) {
            cell.setCellStyle(cellStyle);
        }
        cell.setCellFormula(formula);
    }

    /**
     * @param idx - zero based index
     * @return
     */
    public static String getAlphaforIdx(int idx) {
        String[] alpha = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};
        String formatted = "";
        if (idx < alpha.length) {
            formatted = alpha[idx];
        } else {
            //idx=idx+1;
            int idx1 = (idx / 26) - 1;
            int idx2 = (idx) % 26;
            if (idx1 >= 0) {
                formatted = alpha[idx1];
            }
            if (idx2 >= 0) {
                formatted = formatted + alpha[idx2];
            }
        }
        return formatted;
    }

    /**
     *
     * @param columnIdx 0-based index.
     * @param rowIdx 0-based index.
     * @return
     */
    public String getCellReference(int columnIdx, int rowIdx) {
        return getAlphaforIdx(columnIdx) + getValueRowNumber(columnIdx, rowIdx+1);
    }

    /**
     *
     * @param columnIdx 0-based.
     * @param rowNumber starts from 1(one).
     * @return
     */
    private int getValueRowNumber(int columnIdx, int rowNumber) {
        ArrayList<Range> rangeList = (ArrayList<Range>) mergeIdxMap.get(columnIdx + "");
        if (rangeList == null) {
            return rowNumber;
        } else {
            for (int i = 0; i < rangeList.size(); i++) {
                Range range = (Range) rangeList.get(i);
                if (range.contains(rowNumber)) {
                    return range.fromRowNumber;
                }
            }
        }
        return rowNumber;
    }

    public Cell mergeCells(Sheet sheet, int startRow, int startCell, int endRow, int endCell) {
        //creating rows if not created
        for (int rowIdx = startRow; rowIdx <= endRow; rowIdx++) {
            if (sheet.getRow(rowIdx) == null) {
                sheet.createRow(rowIdx);
            }
        }
        ((Sheet) sheet).addMergedRegion(new CellRangeAddress((short) startRow, (short) startCell, (short) endRow, (short) endCell) );
        Cell startCellObj = getCellAt(sheet.getRow(startRow), startCell);
        Cell cell = null;
        for (int rowIdx = startRow; rowIdx <= endRow; rowIdx++) {
            for (int cellIdx = startCell; cellIdx <= endCell; cellIdx++) {
                cell = getCellAt(sheet.getRow(rowIdx), (short) cellIdx);
                cell.setCellStyle(startCellObj.getCellStyle());
            }
        }
        if (mergeIdxMap.containsKey(startCell + "")) {
            ArrayList<Range> rangeList = (ArrayList<Range>) mergeIdxMap.get(startCell + "");
            rangeList.add(new Range((startRow + 1), (endRow + 1)));
        } else {
            ArrayList<Range> rangeList = new ArrayList<Range>();
            rangeList.add(new Range((startRow + 1), (endRow + 1)));
            mergeIdxMap.put(startCell + "", rangeList);
        }
        return startCellObj;
    }

    /**
     * merges cells and sets each cell with the given cellStyle.
     * @param sheet
     * sheet object in which the cells has to be merged.
     * @param startRowIdx
     * start row Number to merge from.<br>
     * starts from 0.
     * @param startCellIdx
     * start cell number in the start row to merge from.<br>
     * starts from 0.
     * @param endRowIdx
     * end row Number<br>
     * starts from 0.
     * @param endCellIdx
     * end cell number<br>
     * starts from 0.
     * @param cellStyle
     * end cell number<br>
     * starts from 0.
     * @return
     * returns cell object of the startRowIdx, startCellIdx.
     */
    public Cell mergeCells(Sheet sheet, int startRowIdx, int startCellIdx, int endRowIdx, int endCellIdx, CellStyle cellStyle) {
        sheet.addMergedRegion(new CellRangeAddress(startRowIdx, endRowIdx, startCellIdx, endCellIdx));
        Cell cell;
        for (int rowIdx = startRowIdx; rowIdx <= endRowIdx; rowIdx++) {
            if (sheet.getRow(rowIdx) == null) {
                sheet.createRow(rowIdx);
            }
            for (int cellIdx = startCellIdx; cellIdx <= endCellIdx; cellIdx++) {
                cell = getCellAt(sheet.getRow(rowIdx), cellIdx);
                cell.setCellStyle(cellStyle);
            }
        }
        if (mergeIdxMap.containsKey(startCellIdx + "")) {
            ArrayList<Range> rangeList = mergeIdxMap.get(startCellIdx + "");
            rangeList.add(new Range((startRowIdx + 1), (endRowIdx + 1)));
        } else {
            ArrayList<Range> rangeList = new ArrayList<Range>();
            rangeList.add(new Range((startRowIdx + 1), (endRowIdx + 1)));
            mergeIdxMap.put(startCellIdx + "", rangeList);
        }

        return getCellAt(sheet.getRow(startRowIdx), startCellIdx);
    }

    /**
     *
     * @param sheet
     * @param row in which the total to be displayed.
     * @param startRowIdx 0-based. total starts from this row.
     * @param endRowIdx 0-based. total ends to this row.
     * @param colIdx 0-based. the total is applied to the specified column index.
     * @param cellStyle cell style to be applied for the total cell.
     * <br> If <code>null</code> then the style of cell[startRowIdx, colIdx] is applied.
     */
    public void setSumFormula(Sheet sheet, Row row, int startRowIdx, int endRowIdx, int colIdx, CellStyle cellStyle) {
        String totalFormula = "SUM(" + getCellReference(colIdx, startRowIdx) + ":" + getCellReference(colIdx, endRowIdx) + ")";
        if(cellStyle == null) {
            cellStyle  = sheet.getRow(startRowIdx).getCell(colIdx).getCellStyle();
        }
        setCellFormula(row, colIdx, totalFormula, cellStyle);
    }
    private void initialize() {
        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 12);
        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titleFont.setFontName("arial");
        titleFont.setColor(IndexedColors.BLACK.getIndex());

        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 8);
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setFontName("arial");
        headerFont.setColor(IndexedColors.BLACK.getIndex());

        Font textFont = workbook.createFont();
        textFont.setFontHeightInPoints((short) 8);
        textFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
        textFont.setFontName("arial");
        textFont.setColor(IndexedColors.BLACK.getIndex());

        CELL_STYLE_TITLE = workbook.createCellStyle();
        CELL_STYLE_TITLE.setBorderBottom(CellStyle.BORDER_THIN);
        CELL_STYLE_TITLE.setBorderTop(CellStyle.BORDER_THIN);
        CELL_STYLE_TITLE.setBorderLeft(CellStyle.BORDER_THIN);
        CELL_STYLE_TITLE.setBorderRight(CellStyle.BORDER_THIN);
        CELL_STYLE_TITLE.setAlignment(CellStyle.ALIGN_CENTER);
        CELL_STYLE_TITLE.setWrapText(true);
        CELL_STYLE_TITLE.setFont(titleFont);

        CELL_STYLE_HEADER = workbook.createCellStyle();
        CELL_STYLE_HEADER.setBorderBottom(CellStyle.BORDER_THIN);
        CELL_STYLE_HEADER.setBorderTop(CellStyle.BORDER_THIN);
        CELL_STYLE_HEADER.setBorderLeft(CellStyle.BORDER_THIN);
        CELL_STYLE_HEADER.setBorderRight(CellStyle.BORDER_THIN);
        CELL_STYLE_HEADER.setAlignment(CellStyle.ALIGN_CENTER);
        CELL_STYLE_HEADER.setWrapText(true);
        CELL_STYLE_HEADER.setFont(headerFont);
        CELL_STYLE_HEADER.setDataFormat(workbook.createDataFormat().getFormat("@"));

        CELL_STYLE_TEXT = workbook.createCellStyle();
        CELL_STYLE_TEXT.setBorderBottom(CellStyle.BORDER_THIN);
        CELL_STYLE_TEXT.setBorderTop(CellStyle.BORDER_THIN);
        CELL_STYLE_TEXT.setBorderLeft(CellStyle.BORDER_THIN);
        CELL_STYLE_TEXT.setBorderRight(CellStyle.BORDER_THIN);
        CELL_STYLE_TEXT.setAlignment(CellStyle.ALIGN_CENTER);
        CELL_STYLE_TEXT.setWrapText(true);
        CELL_STYLE_TEXT.setFont(textFont);
        CELL_STYLE_TEXT.setDataFormat(workbook.createDataFormat().getFormat("@"));

        CELL_STYLE_NUMBER = workbook.createCellStyle();
        CELL_STYLE_NUMBER.setBorderBottom(CellStyle.BORDER_THIN);
        CELL_STYLE_NUMBER.setBorderTop(CellStyle.BORDER_THIN);
        CELL_STYLE_NUMBER.setBorderLeft(CellStyle.BORDER_THIN);
        CELL_STYLE_NUMBER.setBorderRight(CellStyle.BORDER_THIN);
        CELL_STYLE_NUMBER.setAlignment(CellStyle.ALIGN_RIGHT);
        CELL_STYLE_NUMBER.setWrapText(true);
        CELL_STYLE_NUMBER.setFont(textFont);
        CELL_STYLE_NUMBER.setDataFormat(workbook.createDataFormat().getFormat("###0;[Red](###0)"));

        CELL_STYLE_NUMBER_TOTAL = workbook.createCellStyle();
        CELL_STYLE_NUMBER_TOTAL.setBorderBottom(CellStyle.BORDER_THIN);
        CELL_STYLE_NUMBER_TOTAL.setBorderTop(CellStyle.BORDER_THIN);
        CELL_STYLE_NUMBER_TOTAL.setBorderLeft(CellStyle.BORDER_THIN);
        CELL_STYLE_NUMBER_TOTAL.setBorderRight(CellStyle.BORDER_THIN);
        CELL_STYLE_NUMBER_TOTAL.setAlignment(CellStyle.ALIGN_RIGHT);
        CELL_STYLE_NUMBER_TOTAL.setWrapText(true);
        CELL_STYLE_NUMBER_TOTAL.setFont(headerFont);
        CELL_STYLE_NUMBER_TOTAL.setDataFormat(workbook.createDataFormat().getFormat("###0;[Red](###0)"));

        CELL_STYLE_DATE = workbook.createCellStyle();
        CELL_STYLE_DATE.setBorderBottom(CellStyle.BORDER_THIN);
        CELL_STYLE_DATE.setBorderTop(CellStyle.BORDER_THIN);
        CELL_STYLE_DATE.setBorderLeft(CellStyle.BORDER_THIN);
        CELL_STYLE_DATE.setBorderRight(CellStyle.BORDER_THIN);
        CELL_STYLE_DATE.setAlignment(CellStyle.ALIGN_CENTER);
        CELL_STYLE_DATE.setWrapText(true);
        CELL_STYLE_DATE.setFont(textFont);
        CELL_STYLE_DATE.setDataFormat(workbook.createDataFormat().getFormat("m/d/yy"));

        CELL_STYLE_FLOAT = workbook.createCellStyle();
        CELL_STYLE_FLOAT.setBorderBottom(CellStyle.BORDER_THIN);
        CELL_STYLE_FLOAT.setBorderTop(CellStyle.BORDER_THIN);
        CELL_STYLE_FLOAT.setBorderLeft(CellStyle.BORDER_THIN);
        CELL_STYLE_FLOAT.setBorderRight(CellStyle.BORDER_THIN);
        CELL_STYLE_FLOAT.setAlignment(CellStyle.ALIGN_RIGHT);
        CELL_STYLE_FLOAT.setWrapText(true);
        CELL_STYLE_FLOAT.setFont(textFont);
        CELL_STYLE_FLOAT.setDataFormat(workbook.createDataFormat().getFormat("#,##0.000"));

        CELL_STYLE_FLOAT_PER = workbook.createCellStyle();
        CELL_STYLE_FLOAT_PER.setBorderBottom(CellStyle.BORDER_THIN);
        CELL_STYLE_FLOAT_PER.setBorderTop(CellStyle.BORDER_THIN);
        CELL_STYLE_FLOAT_PER.setBorderLeft(CellStyle.BORDER_THIN);
        CELL_STYLE_FLOAT_PER.setBorderRight(CellStyle.BORDER_THIN);
        CELL_STYLE_FLOAT_PER.setAlignment(CellStyle.ALIGN_RIGHT);
        CELL_STYLE_FLOAT_PER.setWrapText(true);
        CELL_STYLE_FLOAT_PER.setFont(textFont);
        CELL_STYLE_FLOAT_PER.setDataFormat(workbook.createDataFormat().getFormat("0%"));

        CELL_STYLE_NUMERIC = workbook.createCellStyle();
        CELL_STYLE_NUMERIC.setBorderBottom(CellStyle.BORDER_THIN);
        CELL_STYLE_NUMERIC.setBorderTop(CellStyle.BORDER_THIN);
        CELL_STYLE_NUMERIC.setBorderLeft(CellStyle.BORDER_THIN);
        CELL_STYLE_NUMERIC.setBorderRight(CellStyle.BORDER_THIN);
        CELL_STYLE_NUMERIC.setAlignment(CellStyle.ALIGN_RIGHT);
        CELL_STYLE_NUMERIC.setWrapText(true);
        CELL_STYLE_NUMERIC.setFont(textFont);
        CELL_STYLE_NUMERIC.setDataFormat(workbook.createDataFormat().getFormat("#,##0.0000;[Red](#,##0.0000)"));

        CELL_STYLE_CURRENCY_$ = workbook.createCellStyle();
        CELL_STYLE_CURRENCY_$.setBorderBottom(CellStyle.BORDER_THIN);
        CELL_STYLE_CURRENCY_$.setBorderTop(CellStyle.BORDER_THIN);
        CELL_STYLE_CURRENCY_$.setBorderLeft(CellStyle.BORDER_THIN);
        CELL_STYLE_CURRENCY_$.setBorderRight(CellStyle.BORDER_THIN);
        CELL_STYLE_CURRENCY_$.setAlignment(CellStyle.ALIGN_RIGHT);
        CELL_STYLE_CURRENCY_$.setWrapText(true);
        CELL_STYLE_CURRENCY_$.setFont(textFont);
        CELL_STYLE_CURRENCY_$.setDataFormat(workbook.createDataFormat().getFormat("$ #,##0.0000;[Red]$ (#,##0.0000)"));

        CELL_STYLE_CURRENCY_$_TOTAL = workbook.createCellStyle();
        CELL_STYLE_CURRENCY_$_TOTAL.setBorderBottom(CellStyle.BORDER_THIN);
        CELL_STYLE_CURRENCY_$_TOTAL.setBorderTop(CellStyle.BORDER_THIN);
        CELL_STYLE_CURRENCY_$_TOTAL.setBorderLeft(CellStyle.BORDER_THIN);
        CELL_STYLE_CURRENCY_$_TOTAL.setBorderRight(CellStyle.BORDER_THIN);
        CELL_STYLE_CURRENCY_$_TOTAL.setAlignment(CellStyle.ALIGN_RIGHT);
        CELL_STYLE_CURRENCY_$_TOTAL.setWrapText(true);
        CELL_STYLE_CURRENCY_$_TOTAL.setFont(headerFont);
        CELL_STYLE_CURRENCY_$_TOTAL.setDataFormat(workbook.createDataFormat().getFormat("$ #,##0.0000;[Red]$ (#,##0.0000)"));

		CELL_STYLE_CURRENCY_FORMATTED_$ = workbook.createCellStyle();
        CELL_STYLE_CURRENCY_FORMATTED_$.setBorderBottom(CellStyle.BORDER_THIN);
        CELL_STYLE_CURRENCY_FORMATTED_$.setBorderTop(CellStyle.BORDER_THIN);
        CELL_STYLE_CURRENCY_FORMATTED_$.setBorderLeft(CellStyle.BORDER_THIN);
        CELL_STYLE_CURRENCY_FORMATTED_$.setBorderRight(CellStyle.BORDER_THIN);
        CELL_STYLE_CURRENCY_FORMATTED_$.setAlignment(CellStyle.ALIGN_RIGHT);
        CELL_STYLE_CURRENCY_FORMATTED_$.setWrapText(true);
        CELL_STYLE_CURRENCY_FORMATTED_$.setFont(textFont);
        CELL_STYLE_CURRENCY_FORMATTED_$.setDataFormat(workbook.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)"));
    }

    class Range {
        int fromRowNumber; //starts from 1
        int toRowNumber; //starts from 1

        public Range(int fromRowNumber, int toRowNumber) {
            this.fromRowNumber = fromRowNumber;
            this.toRowNumber = toRowNumber;
        }

        /**
         *
         * @param rowNumber starts from 1.
         * @return
         */
        public boolean contains(int rowNumber) {
            return (rowNumber >= fromRowNumber && rowNumber <= toRowNumber);
        }
    }
}
